package com.example.demo.sql;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.*;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.*;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.SelectItem;

import java.util.ArrayList;
import java.util.List;

import static com.example.demo.sql.Constants.*;

public class SqlUtil {

    /**
     * 是否使用AS关键字
     */
    public static final boolean useAs = true;

    public static String getSql(SqlEntity sqlEntity) {
        PlainSelect select = getSelect(sqlEntity);
        return select.toString();
    }

    /**
     * 填充关联的字段
     *
     * @param sqlEntity
     * @return
     */
    public static SqlEntity init(SqlEntity sqlEntity) {
        return sqlEntity;
    }


    public static Table getTable(SqlTableEntity tableEntity) {

        // TODO 写一个缓存，保证相同的表名时，返回的是同一对象
        Table table = new Table(tableEntity.getTableName());
        if (StrUtil.isNotEmpty(tableEntity.getTableAlias())) {
            table.setAlias(new Alias(tableEntity.getTableAlias(), useAs));
        }
        return table;
    }

    public static Table getTable(String tableName, String tableAlias) {

        // TODO 写一个缓存，保证相同的表名时，返回的是同一对象
        Table table = new Table(tableName);
        if (StrUtil.isNotEmpty(tableAlias)) {
            table.setAlias(new Alias(tableAlias, useAs));
        }
        return table;
    }

    public static List<Column> getColumns(List<SqlTableColumnEntity> sqlTableColumnEntityList) {
        List<Column> columnList = new ArrayList<>();
        for (SqlTableColumnEntity sqlTableColumnEntity : sqlTableColumnEntityList) {
            columnList.add(getColumn(sqlTableColumnEntity));
        }
        return columnList;
    }

    public static Column getColumn(SqlTableColumnEntity sqlTableColumnEntity) {

        // TODO 写一个缓存，保证相同的表且相同字段名时，返回的是同一对象
        Table table = getTable(sqlTableColumnEntity.getTable());
        return new Column(table, sqlTableColumnEntity.getColumnName());
    }

    public static Column getColumn(String tableName, String columName) {

        // TODO 写一个缓存，保证相同的表且相同字段名时，返回的是同一对象
        Table table = getTable(tableName, null);
        return new Column(table, columName);
    }

    /**
     * 获取函数column的参数数组
     *
     * @param sqlTableColumnEntity
     * @return
     */
    public static Expression[] getFunctionParameters(SqlTableColumnEntity sqlTableColumnEntity) {

        List<Expression> expressionList = new ArrayList<>();
        Table table = getTable(sqlTableColumnEntity.getTable());

        if (CollectionUtil.isEmpty(sqlTableColumnEntity.getParameterList())) {
            throw new IllegalArgumentException("此Column不是函数Column");
        }

        List<String> parameterList = sqlTableColumnEntity.getParameterList();
        for (String param : parameterList) {
            Column column = getColumn(table.getName(), param);
            expressionList.add(column);
        }
        return expressionList.toArray(new Expression[0]);
    }

    public static List<Join> getJoinList(List<SqlJoinEntity> joinEntityList) {
        List<Join> joinList = new ArrayList<>();
        for (SqlJoinEntity sqlJoinEntity : joinEntityList) {
            Join join = new Join();
            Table joinTable = getTable(sqlJoinEntity.getTable());
            join.setRightItem(joinTable);
            if (JOIN_TYPE_LEFT_JOIN.equals(sqlJoinEntity.getJoinType())) {
                join.setLeft(true);
            } else if (JOIN_TYPE_RIGHT_JOIN.equals(sqlJoinEntity.getJoinType())) {
                join.setRight(true);
            } else if (JOIN_TYPE_INNER_JOIN.equals(sqlJoinEntity.getJoinType())) {
                join.setInner(true);
            } else {
                throw new IllegalArgumentException("不支持的连接方式");
            }
            Column leftColumnEntity = getColumn(sqlJoinEntity.getLeftColumn());
            Column rightColumnEntity = getColumn(sqlJoinEntity.getRightColumn());
            Expression onExpression = new EqualsTo(leftColumnEntity, rightColumnEntity);
            join.addOnExpression(onExpression);
            joinList.add(join);
        }

        return joinList;
    }


    /**
     * 将包含多个逻辑条件的SqlMultiExpression转为jsqlparser的Expression
     *
     * @param multiExpression
     * @return
     */
    public static Expression getExpression(SqlMultiExpressionEntity multiExpression) {
        List<SqlConditionEntity> conditionEntityListList = multiExpression.getConditionList();
        List<Expression> conditionList = toExpression(conditionEntityListList);
        List<String> operators = multiExpression.getOperatorList();
        // TODO 括号逻辑比较复杂，暂时不考虑
        Expression expression = conditionList.get(0);
        for (int i = 1; i < conditionList.size(); i++) {
            if (JOIN_STRING_AND.equals(operators.get(i - 1))) {
                expression = new AndExpression(expression, conditionList.get(i));
            } else if (JOIN_STRING_OR.equals(operators.get(i))) {
                expression = new OrExpression(expression, conditionList.get(i));
            }
        }
        return expression;
    }

    /**
     * 将SqlConditionEntity集合转为jsqlparser的Expression集合
     *
     * @param conditionList
     * @return
     */
    public static List<Expression> toExpression(List<SqlConditionEntity> conditionList) {
        List<Expression> expressionList = new ArrayList<>();
        for (SqlConditionEntity sqlConditionEntity : conditionList) {
            Expression expression = getExpression(sqlConditionEntity);
            expressionList.add(expression);
        }
        return expressionList;
    }

    /**
     * 将SqlConditionEntity转为jsqlparser的Expression
     *
     * @param sqlConditionEntity
     * @return
     */
    public static Expression getExpression(SqlConditionEntity sqlConditionEntity) {
        Expression binaryExpression = getBinaryExpression(sqlConditionEntity);
        return setExpression(binaryExpression, sqlConditionEntity);
    }

    /**
     * 根据operator获取Expression
     * 这个方法的返回类型之所以不是BinaryExpression，是因为LikeExpression和InExpression没有继承BinaryExpression
     *
     * @param sqlConditionEntity
     * @return
     */
    public static Expression getBinaryExpression(SqlConditionEntity sqlConditionEntity) {
        final String operator = sqlConditionEntity.getOperator();
        SqlTableColumnEntity column = sqlConditionEntity.getColumn();
        SqlTableEntity tableEntity = column.getTable();
        Table table = getTable(tableEntity);
        if (OPERATOR_EQ.equals(operator)) {
            return new EqualsTo()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_NE.equals(operator)) {
            return new NotEqualsTo()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_GT.equals(operator)) {
            return new GreaterThan()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_LT.equals(operator)) {
            return new MinorThan()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_GE.equals(operator)) {
            return new GreaterThanEquals()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_LE.equals(operator)) {
            return new MinorThanEquals()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_LIKE.equals(operator)) {
            return new LikeExpression()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else if (OPERATOR_IN.equals(operator)) {
            return new InExpression()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        }else if (OPERATOR_IS_NULL.equals(operator)) {
            return new IsNullExpression()
                    .withLeftExpression(new Column(table, column.getColumnName()));
        } else {
            throw new IllegalArgumentException("不支持的比较运算符");
        }
    }

    /**
     * 设置比较Expression的RightExpression
     * @param expression
     * @param sqlConditionEntity
     * @return
     */
    public static Expression setExpression(Expression expression, SqlConditionEntity sqlConditionEntity) {
        String valueType = sqlConditionEntity.getValueType();
        Object value = sqlConditionEntity.getValue();
        if (RIGHT_VALUE_TYPE_LONG.equals(valueType)) {
            BinaryExpression comparisonOperator = (BinaryExpression) expression;
            comparisonOperator.withRightExpression(new LongValue(value.toString()));
            return comparisonOperator;
        } else if (RIGHT_VALUE_TYPE_DOUBLE.equals(valueType)) {
            BinaryExpression comparisonOperator = (BinaryExpression) expression;
            comparisonOperator.withRightExpression(new DoubleValue(value.toString()));
            return comparisonOperator;
        } else if (RIGHT_VALUE_TYPE_COLLECTION.equals(valueType)) {
            InExpression comparisonOperator = (InExpression) expression;
            ExpressionList itemsList = new ExpressionList((List) value);
            Expression parenthesis = new Parenthesis(itemsList);
            comparisonOperator.withRightExpression(parenthesis);
            return comparisonOperator;
        } else if (RIGHT_VALUE_TYPE_SQL.equals(valueType)) {
            InExpression comparisonOperator = (InExpression) expression;
            String sql = value.toString();
            Expression rightExpression = null;
            try {
                rightExpression = CCJSqlParserUtil.parseCondExpression(sql);
            } catch (JSQLParserException e) {
                throw new RuntimeException(e);
            }
            comparisonOperator.withRightExpression(rightExpression);
            return comparisonOperator;
        } else if (RIGHT_VALUE_TYPE_COLUMN.equals(valueType)) {
            InExpression comparisonOperator = (InExpression) expression;
            SqlTableColumnEntity columnEntity = JSONUtil.toBean(value.toString(), SqlTableColumnEntity.class);
            Table table1 = getTable(columnEntity.getTable());
            comparisonOperator.withRightExpression(new Column(table1, columnEntity.getColumnName()));
            return comparisonOperator;
        } else {
            BinaryExpression comparisonOperator = (BinaryExpression) expression;
            comparisonOperator.withRightExpression(new StringValue(value.toString()));
            return comparisonOperator;
        }
    }

    public static PlainSelect getSelect(SqlEntity sqlEntity) {
        Table mainTable = getTable(sqlEntity.getFrom());
        List<Join> joinList = getJoinList(sqlEntity.getJoinList());
        Expression whereExpression = getExpression(sqlEntity.getWhere().getMultiExpression());
        List<Column> groupByColumnList = getColumns(sqlEntity.getGroupBy().getGroupByList());

        PlainSelect plainSelect = new PlainSelect();
        List<SqlTableColumnEntity> columnEntityListList = sqlEntity.getSelectList();
        List<SelectItem<?>> selectItemList = new ArrayList<>();
        for (SqlTableColumnEntity sqlTableColumnEntity : columnEntityListList) {
            if (CollectionUtil.isEmpty(sqlTableColumnEntity.getParameterList())) {
                // column是表列的情况
                Column column = getColumn(sqlTableColumnEntity);
                SelectItem<Column> columnSelectItem = new SelectItem<>(column, new Alias(sqlTableColumnEntity.getAlias()));
                selectItemList.add(columnSelectItem);
            } else {
                // column是函数的情况
                String functionName = sqlTableColumnEntity.getColumnName();
                Function function = new Function();
                function.setName(functionName);
                Expression[] functionParameters = getFunctionParameters(sqlTableColumnEntity);
                function.setParameters(functionParameters);
            }
        }
        plainSelect.addSelectItems(selectItemList);

        plainSelect.setFromItem(mainTable);
        plainSelect.setJoins(joinList);
        plainSelect.setWhere(whereExpression);
        for (Column column : groupByColumnList) {
            plainSelect.addGroupByColumnReference(column);
        }
        return plainSelect;
    }
}
